Re: problems using a database script
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 25 Dec 2008 22:55:59 +0000 (UTC)
WISEMANOFNARNIA (COHENMARVIN@xxxxxxxxx) writes:
I have a database at work. I scripted it to 3 files - one with users,
one with tables, and one with stored procedures. Then I took the
scripts home and tried to run them. The user script had statements
like:
Create User MyAdmin For Login MyAdmin with Default_schema[dbo]
This failed with the following error:
"MyAdmin is not a valid login or you do not have permission."
What login are they referring to? Are they using mixed mode
authentication?
SQL Server has principals on two levels: 1) on the server level, where
they are commonly referred to as logins 2) on the database level, where
they are referred to as users.
There are two kinds of logins: SQL logins and Windows logins. An SQL
login is defined withing SQL Server itself, whereas an Windows login
is defined by Windows, and merely granted access to SQL Server, which
could be through a Windows group.
As long as we are talking about persons who log in to SQL Server,
to run queries in a database, they need to have a login in SQL Server
and somehow map to a user in the database. An SQL login must have a user
explicitly mapping to the login (unless the guest user is enabled),
whereas a Windows login again can be mapped implicitly.
However, a database user does not have to map to a login. There are
several ways this can happen. You can restore a database on a different
server. An SQL login has a SID which is unique to the server, so users
that maps to SQL logins will be orphaned on the new server. But it is
also possible to create a user and explicitly say WITHOUT LOGIN. This is
handy in a number of situations.
In your case you have script the database on server, and now you are
running the script on your own server. Obviously, you don't have all
logins your server at home that you have on your source server.
You can sort this out by creating these logins on your source servers.
If you don't plan to log in with any of these logins you could also
change the CREATE USER statements to use WITHOUT LOGIN.
Then I imported the tables and the stored procedures.
But the stored procedures were all underlined in red, and when I
hovered my mouse over a table name, it said the name was an invalid
object name. But when I checked the tables, there it was - it had
been created correctly.
When you hovered and you created the tables? Even if you did, you may
need to refresh the Intellisense cache. There is a submenu for
Intellisense somewhere that has this command. There is also a keyboard
shortcut for it, I believe.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: Re: customise primary Key
- Next by Date: Re: customise primary Key
- Previous by thread: customise primary Key
- Next by thread: UID as PK
- Index(es):
Relevant Pages
|
