Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Explanation of roles, schemas etc

Author  Topic 

spierian
Starting Member

5 Posts

Posted - 2013-06-16 : 08:45:23
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-16 : 12:51:23
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 - 2013-06-18 : 10:29:28
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 - 2013-06-18 : 11:01:50
Sorry, should read

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 11:10:32
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 - 2013-06-18 : 12:25:24
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 12:30:23
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 - 2013-06-24 : 16:24:12
James K, thank you very much. Your reply was very helpful.
Go to Top of Page
   

- Advertisement -