Enumerating Role Permissions with SMO

Hi,

I am attempting to enumerate role permissions using SMO. I can enumerate the permissions on a atbel, view, or store procedure no problem. When I try to enumerate the permissions that a role has I get the following error:

'Operation not supported on SQL Server 2000'

Can anyone help me in sorting this out. I had the permissions enumerated in DMO but I just can't seem to get it to work in SMO.

Thanks,

James

Just to make sure that I answer this correctly: are you connecting to SQL Server 2000 or SQL Servert 2005?|||

Hi,

I am connecting to SQL2K. I have not problem getting the permissions on a table, view, stored procedure, or function. Its only when I try to get the permissions for a role or user.

James

|||

SMO has aligned the meaning of User.EnumObjectPermissions with all other objects (such as Table). This means that you would enumerate the permissions that have granted or denied to a principal for a User object; not the permissions that have been granted to the User object (which represents a database user). SQL Server 2005 has implemented this (it was not possible for SQL Server 2000) so that clarifies the message you get.

You should be able to call Database.EnumObjectPermissions(User.Name) to obtain the results that you expect.

|||

Thanks,

that worked a treat.

James

|||

Hi,

I am also working on the table permissions.

ObjectPermissionInfo[] objPer = database.EnumObjectPermissions(database.UserName);

But, from this point how could I display all permissions for a table? Could you please send me a sample code?

Thanks in advance.

sql

Enumerating Role Permissions with SMO

Hi,

I am attempting to enumerate role permissions using SMO. I can enumerate the permissions on a atbel, view, or store procedure no problem. When I try to enumerate the permissions that a role has I get the following error:

'Operation not supported on SQL Server 2000'

Can anyone help me in sorting this out. I had the permissions enumerated in DMO but I just can't seem to get it to work in SMO.

Thanks,

James

Just to make sure that I answer this correctly: are you connecting to SQL Server 2000 or SQL Servert 2005?|||

Hi,

I am connecting to SQL2K. I have not problem getting the permissions on a table, view, stored procedure, or function. Its only when I try to get the permissions for a role or user.

James

|||

SMO has aligned the meaning of User.EnumObjectPermissions with all other objects (such as Table). This means that you would enumerate the permissions that have granted or denied to a principal for a User object; not the permissions that have been granted to the User object (which represents a database user). SQL Server 2005 has implemented this (it was not possible for SQL Server 2000) so that clarifies the message you get.

You should be able to call Database.EnumObjectPermissions(User.Name) to obtain the results that you expect.

|||

Thanks,

that worked a treat.

James

|||

Hi,

I am also working on the table permissions.

ObjectPermissionInfo[] objPer = database.EnumObjectPermissions(database.UserName);

But, from this point how could I display all permissions for a table? Could you please send me a sample code?

Thanks in advance.

Enumerate SQL Server Logins and Permissions

Haven't been able to find the answer to this after sever searches. So decided to open the thread myself.

I am trying to write a report enumerating logins in SQL Server and all database-level and object-level permissions granted to each login.

Can someone tell me what system objects I can query to fetch this information?

TIA.

Hi,

You can get the results by querying the following system views(cata log views):

Database-Level Views:

sys.database_permissions
sys.database_principals

Server-Level Views:

sys.server_permissions
sys.sql_logins
sys.server_principals

Thanks & Regards,

Kiran.Y

|||

That's just what I was looking for. Thanks much.

sql

Enterprise Mgr Not showing Table permissions Correctly

After moving DB's to a new server, the public role was
removed on some, but not all of the tables. We tried
setting the permissions to public in EM but although it
correctly sets it, it does not appear to be set. So now
by using EM I can't tell which tables have the correct
permissions. Has anybody seen this problem where EM does
not correctly show the permissions? Does anyone know how
to resolve this? Also, can anyone show me a way to set
public permissions on all tables through QA?
ThanksYes, I've seen it before. It seems to be some disconnect between
syspermissions and sysprotects. I thought that a way to resolve it might be
to create a new database and copy the user tables into it and grant
permissions in the new database. However, I never got around to doing this.
Public is a role, what permissions would you want to grant to public? Select
to all tables?
GRANT SELECT
ON authors -- you could use a cursor here
TO public
Or, if you have your users already in a group, you could just add that group
to the db_datareader role (if you want everyone to have select).
<paul.j@.aptalaska.com> wrote in message
news:3bcc01c47f14$7f5002c0$a401280a@.phx.gbl...
> After moving DB's to a new server, the public role was
> removed on some, but not all of the tables. We tried
> setting the permissions to public in EM but although it
> correctly sets it, it does not appear to be set. So now
> by using EM I can't tell which tables have the correct
> permissions. Has anybody seen this problem where EM does
> not correctly show the permissions? Does anyone know how
> to resolve this? Also, can anyone show me a way to set
> public permissions on all tables through QA?
> Thankssql