db_ddladmin

Question

db_ddladmin

pamalatha

Old Hand

Points: 377
More actions
September 14, 2011 at 12:35 pm

#247648

I have created a sql user and maped to db_ddladmin database role. I maped other windows users to ddladmin database role.
The problem is sql user is not woking meaning I getting error the "donot have select permission on table". The same role if I maped to db_datareader I could able to select from table.
but the windows users could able to accesses all the table.
Any body know why this happenes?
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DDL_Admin does not grant any select permissions at all. It just grants permission to create, modify and drop objects. If you want select permission as well, you'll need to add the user to the db_datareader role.

Per Books Online, DDL_Admin grants the following (and no other permissions)

ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES

You'll probably find that the windows user is part of a group that has db_datareader and hence it's combined permissions are more than may be immediately apparent.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pamalatha Old Hand Points: 377 More actions · Answer 2

Thank you for the reply Gail

But I doucble check the windows user permission they do no have any permisssion like datareader other than default connect permission let me test with datareader combined with ddladmin permission...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said, you'll probably find that the windows user is part of a windows group that has access to SQL and db_datareader permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

This will show you all the Windows Groups registered as SQL Server Logins that the login you're wondering about belongs to:

EXECUTE AS LOGIN = 'windows_login_name_with_access' ;

GO

SELECT sp.name

FROM sys.server_principals sp

CROSS APPLY (

SELECT IS_MEMBER(sp.name) AS is_a_member

) im

WHERE type_desc = 'WINDOWS_GROUP'

AND im.is_a_member = 1 ;

GO

REVERT

GO

Note: run this while logged in as someone in the sysadmin Fixed Server Role.

Once you have a list of Windows Groups you can research each to see which one may be allowing access to the tables.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato