SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Explanation of roles, schemas etc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spierian
Starting Member

5 Posts

Posted - 06/16/2013 :  08:45:23  Show Profile  Reply with Quote
I need to switch my website from Access to SQL Server 2008. I have managed to create the SQLS db, connect to it, and check/modify the scripts (vbscript) that access it. However I#m stuck on admin issues. The single dbo user I set up is not allowed to to use the SHRINK command in SQL Server Studio Express 2008. MY ISP tells me I need to set up another user with the appropriate permissions. I have no idea how to do that. Can anyone help?

Can anyone suggest a source that gives a simple explanation of users, database roles, schemas, permissions etc?

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 06/16/2013 :  12:51:23  Show Profile  Reply with Quote
Roles, users and users are what SQL server refers to collectively as principals. This page, in particular the link to Principals has good info, including how to create them etc. http://msdn.microsoft.com/en-us/library/bb510589.aspx
Go to Top of Page

spierian
Starting Member

5 Posts

Posted - 06/18/2013 :  10:29:28  Show Profile  Reply with Quote
To James K

Thanks for taking the trouble to reply. I'd already found this document and (like some others) seems to consist mostly of terminology definitions.

In the section "To create a SQL Server login" it says "Right-click the Security folder, point to New, and select Login"

When I right-click the security folder I get:

User
Database role
Application role
Schema
Database audit specification

No "login".

Any suggestions?
Go to Top of Page

spierian
Starting Member

5 Posts

Posted - 06/18/2013 :  11:01:50  Show Profile  Reply with Quote
Sorry, should read

"When I right-click the security folder AND POINT TO NEW I get:"
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 06/18/2013 :  11:10:32  Show Profile  Reply with Quote
Within SSMS object explorer, there are levels of security. Directly under the server node, there is a security folder (which controls the server security) and under each database there is a security folder (which is specific to that database). You are right-clicking on the database level security. Instead find the server level security folder and right-click on that.

Having these two security folders is consistent with Microsoft's approach to SQL server security - take a look at the picture on this page http://msdn.microsoft.com/en-us/library/ms191465.aspx There are 3 levels of security - windows, server, and database. You create logins under the server level security.
Go to Top of Page

spierian
Starting Member

5 Posts

Posted - 06/18/2013 :  12:25:24  Show Profile  Reply with Quote
OK, thanks, I found that. But when I try to create a new log in, I get an error 15247 "user does not have permission to perform this action". I tried with default database set to "master" (which comes up by default) and then to my own database. Same result. In both cases I selected SQL Server authentication.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 06/18/2013 :  12:30:23  Show Profile  Reply with Quote
quote:
Originally posted by spierian

OK, thanks, I found that. But when I try to create a new log in, I get an error 15247 "user does not have permission to perform this action". I tried with default database set to "master" (which comes up by default) and then to my own database. Same result. In both cases I selected SQL Server authentication.

That is probably because the login that you are using to access the server does not have enough privileges to create a new login. To create a login you need ALTER ANY LOGIN permission, or be a member of securityadmin group (or sysadmin).

When you look up the MSDN page for each command/utility, usually somewhere on that page they will also indicate what level of permissions are required to use that command/utility. For create login, this page has that info: http://msdn.microsoft.com/en-us/library/ms189751.aspx
Go to Top of Page

spierian
Starting Member

5 Posts

Posted - 06/24/2013 :  16:24:12  Show Profile  Reply with Quote
James K, thank you very much. Your reply was very helpful.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000