I'm trying to apply some security to my database, so i've created a role, added my user to the role and then set the security rights for the role. Everything work great, the security right behave exactly as defined...
However, when I go back the the Permissions pages, the values that i've already set don't appear in the lists (the all still work). In Fact they don't show up anywhere either in the permissions for the table / sp concerned or the right for the user or role.
Is it just me, or is it a problem with SQL 2005 Management Studio? or can anybody suggest a way of finding the securty rights of any given database object?
Microsoft SQL Server 2005 Beta 2
Microsoft SQL Server Management Studio 9.00.1116.00
Regards
Gary T
Security in SQL Server 2005 is Schema based, try these articles to get started. Hope this helps.
http://www.databasejournal.com/features/mssql/article.php/3481751
http://www.windowsitpro.com/Article/ArticleID/42031/42031.html?Ad=1
|||This has got me completely confused !!!!
In my simple world...
I would create a database role and the give this role the required rights to my database objects i.e the tables (select, insert update etc), stored procedures (execute etc). I would then create user accounts and add them to the required database roles...
CREATE ROLE [MyDB_AccessRole]
...Table def's...
GRANT SELECT ON [dbo].[MyTable] TO [MyDB_AccessRole] ..etc
...SP defs ...
GRANT EXECUTE ON [dbo].[MySP] TO [MyDB_AccessRole] ..etc
CREATE USER [DBUser] FOR LOGIN [DBUser]
EXEC sp_addrolemember N'MyDB_AccessRole, N'DBUser'
So far so good... this still all works in SQL 2005 (althought it nevers shows the rights anywhere?)
Then we have schemas...
CREATE SCHEMA [MyDB_AccessSchema] AUTHORIZATION [MyDB_AccessRole] ?
Is is true that the schema is just like the role? (i.e. A collection of database objects and permissions) if so, why do we need both.
Has anybody any simple examples (for dummies like me) that would explain the different between a role and a schema.
Having tried to create a role, I stiil don;t seem to be able to see the rights the user, role or schema has.
regards
Gary T
No comments:
Post a Comment