March 21, 2013 at 8:37 am
I want to give different permission for different tables within a single database for a user.For example a user has full permission to a table while for another table only read permission.How it can be done within a database?
March 21, 2013 at 8:58 am
you create multiple roles which contain the permissions you want to be able to assign;
adding a user and granting permissions are a three part process.
first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;
users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)
here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.
In your Case, I imagine you want to Create a Role that has permissions to very specific objects, but we'll wait to see if you need to provide more details.
USE [SandBox] --my db for users to do stuff.
CREATE ROLE [ReallyReadOnly]
--give my new role READ permission to ALL tables
EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'
--explicitly DENY access to writing
EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'
--give my new role permission to run the procedures you've created
GRANT EXECUTE TO [ReallyReadOnly]
now that the role is created, we can add a LOGIN to the master database
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'
END
now that a LOGIN exists, lets add a USER, tied to that login, to our database
--make a user in the db for the matching login
CREATE USER [bob] FOR LOGIN [bob]
finally, add our user bob to the role we created
EXEC sp_addrolemember N'ReallyReadOnly', N'bob'
Here's an example for a couple of specific tables:
Exec sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
GRANT SELECT ON MyTable2 TO MyRole
GRANT INSERT, UPDATE, DELETE ON MyTable2 TO MyRole
EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'
EXEC sp_addrolemember 'MyRole', 'Jeff'
Lowell
March 21, 2013 at 8:58 am
You can programatically do it with dynamic SQL or you can copy paste a bunch of lines only changing the table name (Object name)..
Simplified syntax for GRANT
GRANT { ALL [ PRIVILEGES ] }
| permission [ (column [ ,...n ] ) ] [ ,...n ]
[ ON [ class:: ] securable ] TO principal [ ,...n ]
[ WITH GRANT OPTION ] [ AS principal ]
Simplified syntax for DENY
DENY { ALL [ PRIVILEGES ] }
| permission [ (column [ ,...n ] ) ] [ ,...n ]
[ ON [ class:: ] securable ] TO principal [ ,...n ]
[ CASCADE] [ AS principal ]
Just change the object name and repeat the code
--Pra:-):-)--------------------------------------------------------------------------------
March 21, 2013 at 9:02 am
Lowell (3/21/2013)
you create multiple roles which contain the permissions you want to be able to assign;adding a user and granting permissions are a three part process.
first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;
users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)
here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.
In your Case, I imagine you want to Create a Role that has permissions to very specific objects, but we'll wait to see if you need to provide more details.
USE [SandBox] --my db for users to do stuff.
CREATE ROLE [ReallyReadOnly]
--give my new role READ permission to ALL tables
EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'
--explicitly DENY access to writing
EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'
--give my new role permission to run the procedures you've created
GRANT EXECUTE TO [ReallyReadOnly]
now that the role is created, we can add a LOGIN to the master database
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'
END
now that a LOGIN exists, lets add a USER, tied to that login, to our database
--make a user in the db for the matching login
CREATE USER [bob] FOR LOGIN [bob]
finally, add our user bob to the role we created
EXEC sp_addrolemember N'ReallyReadOnly', N'bob'
Here's an example for a couple of specific tables:
Exec sp_addrole 'MyRole'
GRANT SELECT ON MyTable TO MyRole
GRANT SELECT ON MyTable2 TO MyRole
GRANT INSERT, UPDATE, DELETE ON MyTable2 TO MyRole
EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'
EXEC sp_addrolemember 'MyRole', 'Jeff'
Thats a good one.. Thanks for educating
--Pra:-):-)--------------------------------------------------------------------------------
March 24, 2013 at 10:39 pm
Hi Lowell
Thank you for replying. Your method of giving permissions to tables works fine.But if in server roles if i set sysadmin(I did it in SSMS) for that user then those permission given for table not works.All roles created gets neglected. I want permission for most of the database as admin and for some table in about 3 database i want to restrict the access for that user. So i gave sysadmin. So why does it will not work as i expected?How to solve it?
March 25, 2013 at 5:24 am
winmansoft (3/24/2013)
Hi LowellThank you for replying. Your method of giving permissions to tables works fine.But if in server roles if i set sysadmin(I did it in SSMS) for that user then those permission given for table not works.All roles created gets neglected. I want permission for most of the database as admin and for some table in about 3 database i want to restrict the access for that user. So i gave sysadmin. So why does it will not work as i expected?How to solve it?
you can't.
once you give someone sysadmin, no other permissions matter; they can do anything to any object in any database. Also roles are cumulative, so if i'm in a role that is everything + anther containing three items, i still have everything. Deny permissions don't affect a sysadmin.
so if you give someone sysadmin, and then want to take something away, you have to go back to the idea of least permissions:
take away sysadmin, and create teh role(s) for each database which carry the appropriate permissions instead.
people like to grant sysadmin a lot because it solves permission problems instantly, but that's just being lazy.
In general, I grant sysadmin for our developers on their Dev SandBox machines , but not on any servers with real business functions (like production) or admin servers.
Lowell
March 25, 2013 at 5:32 am
Hi Lowel..
You are correct. Everyone looks for an easy method. Now in my case i should give admin for almost all databases and restrict for only some tables in few databses. So i was thinking of any easy method of doing it. But its not possible i think. So now i have to give db_owner for the database for which i want full permission(admin permission) and i have to create roles for only database for which i have to restrict the access.Also in server roles i can give only "public" permission. Is that correct?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply