Sql Server 2008: how do I grant privileges to a username?

sql

database-security

sql-server

role-based-access-control

byAnton Shumikhin·Oct 23, 2024

The SQL GRANT statement regulates access to throw out your permissions party.

-- Our humble invitation to SELECT 
GRANT SELECT ON dbo.YourFancyTable TO Mr.Permissions;

Combine the party flavors for a full-flavored feast:

-- Open bar at the INSERT, UPDATE & DELETE gala
GRANT INSERT, UPDATE, DELETE ON dbo.YourFancyTable TO Mr.Permissions;

Substitute Mr.Permissions with actual username and YourFancyTable with the table you target. For a blanket invite to all tables—SQL's equivalent of cupcakes for everyone—assign them to db_datareader or db_datawriter roles.

-- "You're all VIPs to me," said the database to its users
EXEC sp_addrolemember 'db_datareader', 'Mr.Permissions';

Always use USE YourDatabase; so SQL knows which party palace it's in.

Understanding SQL's guest list

SQL Server roles and privileges are your handpicked party guest list, controlling who gets access to what.

Mixing default roles

For easy access, assign a user to:

db_datareader: All tables open for reading.
db_datawriter: All tables open for writing on.
db_owner: Now you're the owner, here are all the keys!

-- When you invite the whole town to party in your castle
EXEC sp_addrolemember 'db_owner', 'Mr.Permissions';

Crafting unique invites

If INSERT, SELECT, UPDATE, and DELETE were a party, you’d be a generous host. You can invite guests to all or curate an exclusive list.

Invitations via SSMS

SQL Server Management Studio (SSMS) is your graphical party-planner extraordinaire. Right-click on user -> Properties -> User Mapping -> tick the box of the desired roles.

Advanced: Fine-tuning your guest list

Quick-and-dirty party planner

Automating user creation and role assignment can make your life easier, especially when the guest list is massive or keeps changing.

-- "It's my party and I'll script if I want to..."
CREATE LOGIN NewLogin WITH PASSWORD = 'UnCrackableCode@@!!';
CREATE USER NewUser FOR LOGIN NewLogin;
EXEC sp_addrolemember 'db_datareader', 'NewUser';

When the party goes south...

Party fouls can happen — guests being denied entrance or the jukebox falling silent. Ensure you've got these covered before you send out the invites:

Guests (users) must have valid passes (logins).
Your celebration (sp_addrolemember) must be in the right venue (database context).
Are you throwing a party or a circus? Make sure the security-revelry balance is maintained.

explain-codes / Sql / Sql Server 2008: how do I grant privileges to a username?

Linked

How do I grant read access for a user to a database in SQL Server?



Create new user in MySQL and give it full access to one database



Adding a column description



How to turn IDENTITY_INSERT on and off using SQL Server 2008?



Sql server stored procedure return a table

