Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Giving permission for each table within a database Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
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?
Post #1433819
Posted Thursday, March 21, 2013 8:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,887, Visits: 31,832
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1433837
Posted Thursday, March 21, 2013 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
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--------------------------------------------------------------------------------
Post #1433839
Posted Thursday, March 21, 2013 9:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
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--------------------------------------------------------------------------------
Post #1433844
Posted Sunday, March 24, 2013 10:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
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?
Post #1434747
Posted Monday, March 25, 2013 5:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,887, Visits: 31,832
winmansoft (3/24/2013)
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?


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1434833
Posted Monday, March 25, 2013 5:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
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?
Post #1434837
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse