I need help with a permissions problem. I had my network admin create a AD
group with all my developers in it. I then went to the Enterprise Manager,
then to the Login screen and put that AD group into the the SQL Server. Nex
t, I gave that group the Se
rver Role of "Database Creators". There are no databases in the server othe
r than the standard Master, Model, MSDB and Tempdb. Non of these databases
are checked for access.
My developers are connecting to the SQL Server with the Server Explorer in .
NET2003 but cannot make any databases. We are using Windows Only security o
n the database server. The error message says SQLState 42000 and Error 4064
, Cannot open user default
database login failed.
There is no default database because they have not made one yet.Use Enterprise Manager to determine what the default database is for the
developers group. I'd set it to tempdb, to which everyone automatically has
access.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"rich" <anonymous@.discussions.microsoft.com> wrote in message
news:A500F9D8-44C7-4488-A61E-96CFE5647932@.microsoft.com...
I need help with a permissions problem. I had my network admin create a AD
group with all my developers in it. I then went to the Enterprise Manager,
then to the Login screen and put that AD group into the the SQL Server.
Next, I gave that group the Server Role of "Database Creators". There are
no databases in the server other than the standard Master, Model, MSDB and
Tempdb. Non of these databases are checked for access.
My developers are connecting to the SQL Server with the Server Explorer in
.NET2003 but cannot make any databases. We are using Windows Only security
on the database server. The error message says SQLState 42000 and Error
4064, Cannot open user default database login failed.
There is no default database because they have not made one yet.
Showing posts with label permissions. Show all posts
Showing posts with label permissions. Show all posts
Saturday, February 25, 2012
Tuesday, February 14, 2012
Determining Users permissions
While it is possible and easy to answer the question:
"Which Users Have permissions on This Table?" by right-clicking the table,
choosing properties from the menu, then Permissions from the dialog.
Is it possible to answer the question:
"What Permissions does This user have?"
Right-clicking the user and choosing properties lets us choose a tab
"Securables" which is always empty. we can see Server Roles, but not
permissions.
We are using Active Directory Groups to group users into permission-roles.
We cannot use Schemas because a lot of our users use MS Access data projects
to view their data.
There doesn't seem to be any way to determine what permissions a User has,
from the user point of view.
I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
with the new 2005 Management Studio.Hi
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
--http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
ef/ts_pa-pz_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
----
--
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
> While it is possible and easy to answer the question:
> "Which Users Have permissions on This Table?" by right-clicking the table,
> choosing properties from the menu, then Permissions from the dialog.
> Is it possible to answer the question:
> "What Permissions does This user have?"
> Right-clicking the user and choosing properties lets us choose a tab
> "Securables" which is always empty. we can see Server Roles, but not
> permissions.
> We are using Active Directory Groups to group users into permission-roles.
> We cannot use Schemas because a lot of our users use MS Access data
> projects
> to view their data.
> There doesn't seem to be any way to determine what permissions a User has,
> from the user point of view.
> I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
> with the new 2005 Management Studio.
>|||Ok, thats all very well for coders, but for example, in Enterprise Manager,
if someone asks me "What permissions on the database does Joe Bloggs in
Accounts have?" I just look up Joe Blogs login, right-click and choose Manag
e
Permissions. I check the box for only showing permissions explicitly defined
and there it is.
I can't DO that in Management studio.
I had a quick look through the functions in master, but the permissions ones
just seem to be concerned with the *current* user. I can see the value of
these... do the test, and if the can, then do the action.
But for a harrased admin being asked the above question... what can be done?
I don't mind cobbling up soem T-SQL to return the perms if required, but it
must be able to be run for any login, not jsut the current one.
BAzz
"Uri Dimant" wrote:
> Hi
> IF PERMISSIONS()&2=2
> CREATE TABLE test_table (col1 INT)
> ELSE
> PRINT 'ERROR: The current user cannot create a table.'
> --
> This example determines whether the current user can
> grant the INSERT permission on the authors table to another user.
> IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
> PRINT 'INSERT on authors is grantable.'
> ELSE
> PRINT 'You may not GRANT INSERT permissions on authors.'
> --http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsq
lref/ts_pa-pz_6f78.asp>
> How can I retrieve a list of objects and permissions for a specified role
?
> ----
--
> In SQL Server 2005, you can use the Has_Perms_By_Name() function
> (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
>
> This is an example of usage:
> SELECT o.SchemaAndName,
> has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
> FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
> SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
> FROM sys.objects
> WHERE type = 'P') AS o
>
>
>
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
>
>|||BAzz,
Assuming that you are a system administrator for the SQL Server, you can
execute in the context of the login that you are checking. Here is a
sample:
EXECUTE AS login='MYDOMAIN\MyLogin'
SELECT name, is_member(name) FROM sys.database_principals
WHERE is_member (name) = 1
SELECT name, (permissions(object_id)) FROM sys.objects
WHERE permissions(object_id) > 0
REVERT
FWIW, RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...[vbcol=seagreen]
> Ok, thats all very well for coders, but for example, in Enterprise
> Manager,
> if someone asks me "What permissions on the database does Joe Bloggs in
> Accounts have?" I just look up Joe Blogs login, right-click and choose
> Manage
> Permissions. I check the box for only showing permissions explicitly
> defined
> and there it is.
> I can't DO that in Management studio.
> I had a quick look through the functions in master, but the permissions
> ones
> just seem to be concerned with the *current* user. I can see the value of
> these... do the test, and if the can, then do the action.
> But for a harrased admin being asked the above question... what can be
> done?
> I don't mind cobbling up soem T-SQL to return the perms if required, but
> it
> must be able to be run for any login, not jsut the current one.
> BAzz
>
> "Uri Dimant" wrote:
>|||Thats a great help, thanks. the Execute as... will do the trick in a
parameterised stored proc that I can have return a formatted list of perms..
Makes me wonder, though, why the functionality was removed from the 2005
Management studio.
"Russell Fields" wrote:
> BAzz,
> Assuming that you are a system administrator for the SQL Server, you can
> execute in the context of the login that you are checking. Here is a
> sample:
> EXECUTE AS login='MYDOMAIN\MyLogin'
> SELECT name, is_member(name) FROM sys.database_principals
> WHERE is_member (name) = 1
> SELECT name, (permissions(object_id)) FROM sys.objects
> WHERE permissions(object_id) > 0
> REVERT
> FWIW, RLF
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...
>
>|||FWIW, I wonder, too. The current Studio behavior is not helpful to me
either.
RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:0FF6C000-6010-4119-B1DB-E84047507080@.microsoft.com...[vbcol=seagreen]
> Thats a great help, thanks. the Execute as... will do the trick in a
> parameterised stored proc that I can have return a formatted list of
> perms..
> Makes me wonder, though, why the functionality was removed from the 2005
> Management studio.
>
> "Russell Fields" wrote:
>
"Which Users Have permissions on This Table?" by right-clicking the table,
choosing properties from the menu, then Permissions from the dialog.
Is it possible to answer the question:
"What Permissions does This user have?"
Right-clicking the user and choosing properties lets us choose a tab
"Securables" which is always empty. we can see Server Roles, but not
permissions.
We are using Active Directory Groups to group users into permission-roles.
We cannot use Schemas because a lot of our users use MS Access data projects
to view their data.
There doesn't seem to be any way to determine what permissions a User has,
from the user point of view.
I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
with the new 2005 Management Studio.Hi
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
--http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
ef/ts_pa-pz_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
----
--
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
> While it is possible and easy to answer the question:
> "Which Users Have permissions on This Table?" by right-clicking the table,
> choosing properties from the menu, then Permissions from the dialog.
> Is it possible to answer the question:
> "What Permissions does This user have?"
> Right-clicking the user and choosing properties lets us choose a tab
> "Securables" which is always empty. we can see Server Roles, but not
> permissions.
> We are using Active Directory Groups to group users into permission-roles.
> We cannot use Schemas because a lot of our users use MS Access data
> projects
> to view their data.
> There doesn't seem to be any way to determine what permissions a User has,
> from the user point of view.
> I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
> with the new 2005 Management Studio.
>|||Ok, thats all very well for coders, but for example, in Enterprise Manager,
if someone asks me "What permissions on the database does Joe Bloggs in
Accounts have?" I just look up Joe Blogs login, right-click and choose Manag
e
Permissions. I check the box for only showing permissions explicitly defined
and there it is.
I can't DO that in Management studio.
I had a quick look through the functions in master, but the permissions ones
just seem to be concerned with the *current* user. I can see the value of
these... do the test, and if the can, then do the action.
But for a harrased admin being asked the above question... what can be done?
I don't mind cobbling up soem T-SQL to return the perms if required, but it
must be able to be run for any login, not jsut the current one.
BAzz
"Uri Dimant" wrote:
> Hi
> IF PERMISSIONS()&2=2
> CREATE TABLE test_table (col1 INT)
> ELSE
> PRINT 'ERROR: The current user cannot create a table.'
> --
> This example determines whether the current user can
> grant the INSERT permission on the authors table to another user.
> IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
> PRINT 'INSERT on authors is grantable.'
> ELSE
> PRINT 'You may not GRANT INSERT permissions on authors.'
> --http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsq
lref/ts_pa-pz_6f78.asp>
> How can I retrieve a list of objects and permissions for a specified role
?
> ----
--
> In SQL Server 2005, you can use the Has_Perms_By_Name() function
> (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
>
> This is an example of usage:
> SELECT o.SchemaAndName,
> has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
> FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
> SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
> FROM sys.objects
> WHERE type = 'P') AS o
>
>
>
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
>
>|||BAzz,
Assuming that you are a system administrator for the SQL Server, you can
execute in the context of the login that you are checking. Here is a
sample:
EXECUTE AS login='MYDOMAIN\MyLogin'
SELECT name, is_member(name) FROM sys.database_principals
WHERE is_member (name) = 1
SELECT name, (permissions(object_id)) FROM sys.objects
WHERE permissions(object_id) > 0
REVERT
FWIW, RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...[vbcol=seagreen]
> Ok, thats all very well for coders, but for example, in Enterprise
> Manager,
> if someone asks me "What permissions on the database does Joe Bloggs in
> Accounts have?" I just look up Joe Blogs login, right-click and choose
> Manage
> Permissions. I check the box for only showing permissions explicitly
> defined
> and there it is.
> I can't DO that in Management studio.
> I had a quick look through the functions in master, but the permissions
> ones
> just seem to be concerned with the *current* user. I can see the value of
> these... do the test, and if the can, then do the action.
> But for a harrased admin being asked the above question... what can be
> done?
> I don't mind cobbling up soem T-SQL to return the perms if required, but
> it
> must be able to be run for any login, not jsut the current one.
> BAzz
>
> "Uri Dimant" wrote:
>|||Thats a great help, thanks. the Execute as... will do the trick in a
parameterised stored proc that I can have return a formatted list of perms..
Makes me wonder, though, why the functionality was removed from the 2005
Management studio.
"Russell Fields" wrote:
> BAzz,
> Assuming that you are a system administrator for the SQL Server, you can
> execute in the context of the login that you are checking. Here is a
> sample:
> EXECUTE AS login='MYDOMAIN\MyLogin'
> SELECT name, is_member(name) FROM sys.database_principals
> WHERE is_member (name) = 1
> SELECT name, (permissions(object_id)) FROM sys.objects
> WHERE permissions(object_id) > 0
> REVERT
> FWIW, RLF
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...
>
>|||FWIW, I wonder, too. The current Studio behavior is not helpful to me
either.
RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:0FF6C000-6010-4119-B1DB-E84047507080@.microsoft.com...[vbcol=seagreen]
> Thats a great help, thanks. the Execute as... will do the trick in a
> parameterised stored proc that I can have return a formatted list of
> perms..
> Makes me wonder, though, why the functionality was removed from the 2005
> Management studio.
>
> "Russell Fields" wrote:
>
Determining permissions through Stored Procedures
Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)